package com.probiz.estore.inventory.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.probiz.estore.common.model.inventory.Inventory;
import com.probiz.estore.common.model.inventory.InventorySearchCriteria;
import com.probiz.estore.common.model.product.ProductSku;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.search.SearchCriteria;
import com.probiz.estore.inventory.dao.InventoryDao;

/**
 * Dao implementation for Inventory.
*/
public class InventoryDaoImpl extends HibernateGenericDaoImpl<Inventory> implements InventoryDao {
	@Override
	protected void initFilterHqlMap() {
		StringBuffer lowSb = new StringBuffer();
		lowSb.append("from ProductSku ps where (ps.product.status=1) ");
		lowSb.append(" and (ps.inventory.quantityOnHand is not null)");
		lowSb.append(" and ( (ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<=(case when ps.product.minOrderQuantity is not null then ps.product.minOrderQuantity else 1 end))");
		lowSb.append(" or  (ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<=ps.inventory.reorderMinimnm) )");
		
		StringBuffer lackSb = new StringBuffer();
		lackSb.append("from ProductSku ps where (ps.product.status=1) ");
		lackSb.append(" and (ps.inventory.quantityOnHand is not null)");
		lackSb.append(" and ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<(case when ps.product.minOrderQuantity is not null then ps.product.minOrderQuantity else 1 end)");
		
		filterHqlMap.put(Inventory.FilterHqlName.COUNT_LOW_STOCK, lowSb.toString());
		filterHqlMap.put(Inventory.FilterHqlName.COUNT_LACK_STOCK, lackSb.toString());
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.inventory.dao.InventoryDao#getLowStockProductSkuLimit(java.lang.Integer)
	 */
	public List<ProductSku> getLowStockProductSkuLimit(final Integer maxSize) {
		List list;
		list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer lowSb = new StringBuffer();
				lowSb.append("from ProductSku ps where (ps.product.status=1) ");
				lowSb.append(" and (ps.inventory.quantityOnHand is not null)");
				lowSb.append(" and ( (ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<=(case when ps.product.minOrderQuantity is not null then ps.product.minOrderQuantity else 1 end))");
				lowSb.append(" or  (ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<=ps.inventory.reorderMinimnm) )");
				Query query = session.createQuery(lowSb.toString());
				query.setMaxResults(maxSize);
				List<ProductSku> rows = (List<ProductSku>) query.list();
				return rows;
			}

		});
		return list;
	}
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.inventory.dao.InventoryDao#getLowStockProductSkuLimit(java.lang.Integer)
	 */
	public List<ProductSku> getLackStockProductSkuLimit(final Integer maxSize) {
		List list;
		list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer lackSb = new StringBuffer();
				lackSb.append("from ProductSku ps where (ps.product.status=1) ");
				lackSb.append(" and (ps.inventory.quantityOnHand is not null)");
				lackSb.append(" and ps.inventory.quantityOnHand - ps.inventory.allocatedQuantity<(case when ps.product.minOrderQuantity is not null then ps.product.minOrderQuantity else 1 end)");
				Query query = session.createQuery(lackSb.toString());
				query.setMaxResults(maxSize);
				List<ProductSku> rows = (List<ProductSku>) query.list();
				return rows;
			}

		});
		return list;
	}
	
	
	
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.inventory.dao.InventoryDao#searchInventory(com.probiz.estore.common.model.inventory.InventorySearchCriteria, com.probiz.estore.core.search.SearchCriteria)
	 */
	public List<ProductSku> searchInventory(InventorySearchCriteria inventorySearchCriteria ,SearchCriteria searchCriteria){
		
		StringBuffer sb = new StringBuffer();
		sb.append("from Inventory ");
		
		searchCriteria.setHql(sb.toString());
		List<ProductSku> list = searchByCriteria(searchCriteria);
		return list;

	}
	
	

}
